/*
	This procedure is used for to insert to update or insert 
	Transit Lots in different Process
	EXEC sp_create_update_data_entry_shipped_lot 19,1,'FAB','G3VCHA.1','G3VCHA.1',null,null,25,'03/17/2011',null,null,null,null,null,'UTAC','1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;',-1,0,null,'Admin'
*/
Alter proc sp_create_update_data_entry_shipped_lot (
 	
	@PROCESS_STEP_ID	INT,
	@LOCATION_ID		INT,
	@PROCESSTYPE		VARCHAR(50),
	@FAB_LOT_NUMBER		VARCHAR(50),
	@VENDOR_LOT_NUMBER	VARCHAR(50),
 	@PO_No			VARCHAR(50),
 	@SHOP_ORDER_NO 		VARCHAR(50),
	@SHIP_QTY 		INT,
	@SHIP_DATE 		DATETIME,
 	@INVOICE_NUMBER 	VARCHAR(50),
	@FLIGHT_NO		VARCHAR(50),
	@ETD 			DATETIME,
 	@ETA 			DATETIME,
	@CUSTOMER_SO		VARCHAR(50),
 	@NEXT_LOCATION 		VARCHAR(50), 	
 	@WAFER_ID 		VARCHAR(100),
 	@SHIPPEDLOTID 		INT,
	@SHIP_DIE_QTY		INT,
 	@COMMENTS 		VARCHAR(3000),
	@USER_NAME	VARCHAR(50)
 )
 
 
 AS
 BEGIN
 
 	DECLARE @LOT_ID 	INT
	DECLARE @LOT_NUMBER 	VARCHAR(50)
	declare @po_number	varchar(50) 	
	declare @BASE_PROCESS_TYE	varchar(50) 	
	
	SELECT @BASE_PROCESS_TYE = Base_Process_Type FROM PROCESS_TYPES_TABLE WHERE Process_Type = @PROCESSTYPE
	/*
		Here in FAB Process FabLotNumber considering as Lot Number and rest of the Process
		Lot Number is Vendor Lot Number

	*/
	IF @BASE_PROCESS_TYE = 'FAB' OR @BASE_PROCESS_TYE = 'SORT' 
		OR @BASE_PROCESS_TYE = 'Die Bank' OR @BASE_PROCESS_TYE = 'Wafer Store' OR @PROCESSTYPE = 'Back Grind'
	BEGIN
		SET @LOT_NUMBER = @FAB_LOT_NUMBER
	END
	ELSE
	BEGIN
		SET @LOT_NUMBER = @VENDOR_LOT_NUMBER
	END
	/* Here to avoid the Default date for ETA and ETD*/
	IF @ETA = ''
	BEGIN
		SET @ETA = NULL
	END
	IF @ETD = ''
	BEGIN
	
		SET @ETD = NULL
	END

	IF @SHIPPEDLOTID <= 0
	BEGIN
		/*to get the lot id */
		SELECT @LOT_ID = LOT_ID FROM LOT_TABLE WHERE PROCESSSTEP_ID = @PROCESS_STEP_ID AND LOCATION_ID = @LOCATION_ID AND LOT_NUMBER = @LOT_NUMBER
		IF @LOT_ID IS NULL
	 	BEGIN
			IF @BASE_PROCESS_TYE = 'FAB' OR @BASE_PROCESS_TYE = 'SORT' 
					OR @BASE_PROCESS_TYE = 'Die Bank' OR @BASE_PROCESS_TYE = 'Wafer Store' OR @PROCESSTYPE = 'Back Grind'
			BEGIN
					INSERT INTO LOT_TABLE (PROCESSSTEP_ID, LOCATION_ID, LOT_NUMBER, FAB_LOT_NUMBER, VENDOR_LOT_NUMBER, WAFER_QTY_IN, DATE_IN, GOOD_DIE_QTY,LOT_CURRENT_STATUS, TIMESTAMP, LOT_ENTERED_BY, LOT_CATEGORY, PO_Number, Shop_Order_Number, user_name) 
						VALUES (@PROCESS_STEP_ID, @LOCATION_ID, @LOT_NUMBER, @FAB_LOT_NUMBER, @VENDOR_LOT_NUMBER, @SHIP_QTY, @SHIP_DATE, @SHIP_DIE_QTY, 'Close', CURRENT_TIMESTAMP, 0, 'PROD', @PO_NO, @SHOP_ORDER_NO, @User_name)
					SELECT @LOT_ID = @@IDENTITY
			END
			ELSE
			BEGIN
					INSERT INTO LOT_TABLE (PROCESSSTEP_ID, LOCATION_ID, LOT_NUMBER, FAB_LOT_NUMBER, VENDOR_LOT_NUMBER, QTY_IN, DATE_IN, LOT_CURRENT_STATUS, TIMESTAMP, LOT_ENTERED_BY, LOT_CATEGORY, PO_Number, Shop_Order_Number, user_name) 
						VALUES (@PROCESS_STEP_ID, @LOCATION_ID, @LOT_NUMBER, @FAB_LOT_NUMBER, @VENDOR_LOT_NUMBER, @SHIP_QTY, @SHIP_DATE, 'Close', CURRENT_TIMESTAMP, 0, 'PROD', @PO_NO, @SHOP_ORDER_NO, @User_name)
					SELECT @LOT_ID = @@IDENTITY
			END
				
	 	END

		DECLARE @GENERATE_SHIP_LOT_BY_WAFER_ID VARCHAR(5)
		DECLARE @WAFERIDS_AS_COMMA VARCHAR(150)
		DECLARE @WAFER_NO		   VARCHAR(5)	
		DECLARE @WAFER_IDS_TABLE   TABLE ( WAFER_ID INT )
		DECLARE @LOTCURSOR CURSOR

		SET @WAFERIDS_AS_COMMA = REPLACE(@WAFER_ID, ';',',')
		INSERT INTO @WAFER_IDS_TABLE SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING( @WAFERIDS_AS_COMMA  )
		SELECT @GENERATE_SHIP_LOT_BY_WAFER_ID = ISNULL(PROPERTY_VALUE,'FALSE') FROM CONFIGURATION_TABLE WHERE PROPERTY_KEY = 'scalar.chiptrack.operations.shippedLot.showShippedLotByWafer'
		
		IF @LOT_ID IS NOT NULL
		BEGIN
			IF @BASE_PROCESS_TYE = 'FAB' OR @BASE_PROCESS_TYE = 'SORT' 
				OR @BASE_PROCESS_TYE = 'Die Bank' OR @BASE_PROCESS_TYE = 'Wafer Store' OR @PROCESSTYPE = 'Back Grind'
			BEGIN
				
				IF @GENERATE_SHIP_LOT_BY_WAFER_ID = 'TRUE'
				BEGIN
					SET @LOTCURSOR = CURSOR FOR SELECT WAFER_ID FROM @WAFER_IDS_TABLE
					OPEN @LOTCURSOR
					FETCH NEXT FROM @LOTCURSOR INTO @WAFER_NO
					WHILE @@FETCH_STATUS = 0
					BEGIN
						
						select @shippedLotID = ISNULL(ShippedLot_ID,0) from Shipped_Lot where Lot_ID = @lot_id and status = 'TRANSIT' and Ship_Status = 0 and Ship_Date = @ship_date and Ship_Qty = 1 AND Wafer_Id = @WAFER_NO
						
						IF @shippedLotID > 0
						BEGIN
								UPDATE SHIPPED_LOT SET SHIP_DATE = @SHIP_DATE, SHIP_QTY = 1, Ship_Good_Die_Qty = @SHIP_DIE_QTY, Ship_Drop_Out = 0,
								SL_NextLocation = @NEXT_LOCATION, SL_Flight_Number = @FLIGHT_NO, SL_ETD = @ETD, SL_ETA = @ETA, Wafer_Id = @WAFER_NO, SL_Invoice_Number = @INVOICE_NUMBER, SL_Customer_SO = @CUSTOMER_SO  WHERE SHIPPEDLOT_ID = @SHIPPEDLOTID 
						END
						ELSE
						BEGIN
							INSERT INTO SHIPPED_LOT( LOT_ID, SHIP_DATE, STATUS, SHIP_QTY, TIMESTAMP, SHIP_STATUS, Ship_Good_Die_Qty, SL_CUSTOMER_SO, SL_INVOICE_NUMBER, SL_NEXTLOCATION, SL_ETD, SL_ETA, WAFER_ID, SL_Flight_Number ) 
								VALUES( @LOT_ID, @SHIP_DATE, 'TRANSIT', 1, CURRENT_TIMESTAMP, 0, @SHIP_DIE_QTY, @CUSTOMER_SO, @INVOICE_NUMBER, @NEXT_LOCATION, @ETD, @ETA, @WAFER_NO, @FLIGHT_NO  )
							SET @SHIPPEDLOTID = ( SELECT @@IDENTITY )
						END
						SET @WAFER_NO = NULL
						SET @shippedLotID = NULL
						FETCH NEXT FROM @LOTCURSOR INTO @WAFER_NO
					END
					CLOSE @LOTCURSOR
					DEALLOCATE @LOTCURSOR	
					DELETE FROM @WAFER_IDS_TABLE
				END
				ELSE
				BEGIN
					select @shippedLotID = ShippedLot_ID from Shipped_Lot where Lot_ID = @lot_id and status = 'TRANSIT' and Ship_Status = 0 and Ship_Date = @ship_date and Ship_Qty = @ship_qty AND Wafer_Id = @WAFER_ID
					IF @shippedLotID > 0
					BEGIN
							UPDATE SHIPPED_LOT SET SHIP_DATE = @SHIP_DATE, SHIP_QTY = @SHIP_QTY, Ship_Good_Die_Qty = @SHIP_DIE_QTY, Ship_Drop_Out = 0,
							SL_NextLocation = @NEXT_LOCATION, SL_Flight_Number = @FLIGHT_NO, SL_ETD = @ETD, SL_ETA = @ETA, Wafer_Id = @WAFER_ID, SL_Invoice_Number = @INVOICE_NUMBER, SL_Customer_SO = @CUSTOMER_SO  WHERE SHIPPEDLOT_ID = @SHIPPEDLOTID
					END
					ELSE
					BEGIN
						INSERT INTO SHIPPED_LOT( LOT_ID, SHIP_DATE, STATUS, SHIP_QTY, TIMESTAMP, SHIP_STATUS, Ship_Good_Die_Qty, SL_CUSTOMER_SO, SL_INVOICE_NUMBER, SL_NEXTLOCATION, SL_ETD, SL_ETA, WAFER_ID, SL_Flight_Number ) 
							VALUES( @LOT_ID, @SHIP_DATE, 'TRANSIT', @SHIP_QTY, CURRENT_TIMESTAMP, 0, @SHIP_DIE_QTY, @CUSTOMER_SO, @INVOICE_NUMBER, @NEXT_LOCATION, @ETD, @ETA, @WAFER_ID, @FLIGHT_NO  )
						SET @SHIPPEDLOTID = ( SELECT @@IDENTITY )
					END
				END
			END
			ELSE
			BEGIN
				select @shippedLotID = ShippedLot_ID from Shipped_Lot where Lot_ID = @lot_id and status = 'TRANSIT' and Ship_Status = 0 and Ship_Date = @ship_date and Ship_Qty = @ship_qty
				IF @shippedLotID > 0
				BEGIN
						UPDATE SHIPPED_LOT SET SHIP_DATE = @SHIP_DATE, SHIP_QTY = @SHIP_QTY, Ship_Good_Die_Qty = @SHIP_DIE_QTY, Ship_Drop_Out = 0,
						SL_NextLocation = @NEXT_LOCATION, SL_Flight_Number = @FLIGHT_NO, SL_ETD = @ETD, SL_ETA = @ETA, Wafer_Id = @WAFER_ID, SL_Invoice_Number = @INVOICE_NUMBER, SL_Customer_SO = @CUSTOMER_SO  WHERE SHIPPEDLOT_ID = @SHIPPEDLOTID
				END
				ELSE
				BEGIN
					INSERT INTO SHIPPED_LOT( LOT_ID, SHIP_DATE, STATUS, SHIP_QTY, TIMESTAMP, SHIP_STATUS, Ship_Good_Die_Qty, SL_CUSTOMER_SO, SL_INVOICE_NUMBER, SL_NEXTLOCATION, SL_ETD, SL_ETA, WAFER_ID, SL_Flight_Number ) 
						VALUES( @LOT_ID, @SHIP_DATE, 'TRANSIT', @SHIP_QTY, CURRENT_TIMESTAMP, 0, @SHIP_DIE_QTY, @CUSTOMER_SO, @INVOICE_NUMBER, @NEXT_LOCATION, @ETD, @ETA, @WAFER_ID, @FLIGHT_NO  )
					SET @SHIPPEDLOTID = ( SELECT @@IDENTITY )
				END
			END
				
			UPDATE LOT_TABLE SET LOT_COMMENTS = @COMMENTS, LOT_CURRENT_STATUS = 'Close', user_name = @User_name WHERE LOT_ID = @LOT_ID
	
		END
	END
	ELSE
	BEGIN		
		SELECT @LOT_ID = LOT_ID FROM SHIPPED_LOT WHERE SHIPPEDLOT_ID = @SHIPPEDLOTID
		
		UPDATE LOT_TABLE SET LOT_COMMENTS = @COMMENTS, LOT_CURRENT_STATUS = 'Close', user_name = @User_name WHERE LOT_ID = @LOT_ID

		IF @GENERATE_SHIP_LOT_BY_WAFER_ID = 'TRUE'
		BEGIN
			SET @LOTCURSOR = CURSOR FOR SELECT WAFER_ID FROM @WAFER_IDS_TABLE
			OPEN @LOTCURSOR
			FETCH NEXT FROM @LOTCURSOR INTO @WAFER_NO
			WHILE @@FETCH_STATUS = 0
			BEGIN
				UPDATE SHIPPED_LOT SET SHIP_DATE = @SHIP_DATE, SHIP_QTY = 1, Ship_Good_Die_Qty = @SHIP_DIE_QTY, SL_CUSTOMER_SO = @CUSTOMER_SO, SL_INVOICE_NUMBER = @INVOICE_NUMBER, SL_NEXTLOCATION = @NEXT_LOCATION, SL_ETD = @ETD, SL_ETA = @ETA, WAFER_ID = @WAFER_NO, SL_Flight_Number = @FLIGHT_NO  WHERE SHIPPEDLOT_ID = @SHIPPEDLOTID AND WAFER_ID = @WAFER_NO
				SET @WAFER_NO = NULL
				FETCH NEXT FROM @LOTCURSOR INTO @WAFER_NO
			END
			CLOSE @LOTCURSOR
			DEALLOCATE @LOTCURSOR	
			DELETE FROM @WAFER_IDS_TABLE
		END
		ELSE
		BEGIN
			UPDATE SHIPPED_LOT SET SHIP_DATE = @SHIP_DATE, SHIP_QTY = @SHIP_QTY, Ship_Good_Die_Qty = @SHIP_DIE_QTY, SL_CUSTOMER_SO = @CUSTOMER_SO, SL_INVOICE_NUMBER = @INVOICE_NUMBER, SL_NEXTLOCATION = @NEXT_LOCATION, SL_ETD = @ETD, SL_ETA = @ETA, WAFER_ID = @WAFER_ID, SL_Flight_Number = @FLIGHT_NO  WHERE SHIPPEDLOT_ID = @SHIPPEDLOTID		
		END
	END

	/* Closing Transit Lots and Previous Process Lots*/ 	
	--Exec SP_CLOSE_PREVIOUSPROCESS_AND_TRANSITLOTS  @LOT_NUMBER, @FAB_LOT_NUMBER, @SHIP_QTY,  @SHIP_DATE 
	
 END
 




